CREATE OR REPLACE PACKAGE StepLogicHandler IS

--===============================================================
-- Package information
--===============================================================
FUNCTION Version RETURN VARCHAR2
;
--===============================================================
-- Type declarations
--===============================================================

--===============================================================
-- Function and Procedure declarations
--===============================================================
PROCEDURE InsertInsertionDetails(ContainerId IN Container.ContainerId%TYPE
                                 , AllowQtyOverride IN A_WIPLotDetailsTemp.AllowQtyOverride%TYPE
                                 , QtyToProcess IN A_WIPLotDetailsTemp.QtyToProcess%TYPE
                                 , ProcessTypeName IN A_ProcessType.ProcessTypeName%TYPE
                                 , ProcessStatus IN A_WIPLotDetailsTemp.ProcessStatus%TYPE DEFAULT 'ACTIVE'
                                 , TestStatus IN A_WIPLotDetailsTemp.TestStatus%TYPE DEFAULT ''
                                 , WIPTestStatus IN A_WIPLotDetailsTemp.WIPTestStatus%TYPE DEFAULT ''
                                 , TestPlan IN A_WIPLotDetailsTemp.WIPTestStatus%TYPE DEFAULT ''
                                 , TestSubPlan IN A_WIPLotDetailsTemp.WIPTestStatus%TYPE DEFAULT ''
                                 , ValidQtys IN VARCHAR2 DEFAULT ''
                                 , MinQtyToProcess IN A_WIPLotDetailsTemp.MinQtyToProcess%TYPE DEFAULT 0
                                 , MaxQtyToProcess IN A_WIPLotDetailsTemp.MaxQtyToProcess%TYPE DEFAULT 0)
;
FUNCTION SetInsertionDetails (ContainerName IN Container.ContainerName%TYPE
                              , MainQty IN Container.Qty%TYPE
                              , SpecName IN SpecBase.SpecName%TYPE
                              , StepLogicName IN A_StepLogic.StepLogicName%TYPE
                              , CurrentInsertionNumber IN Container.InsertionNumber%TYPE
                              , ProcessTypeNames IN VARCHAR2
                              , ProcessStatuses IN VARCHAR2)
RETURN VARCHAR2
;
FUNCTION VerifyCompleteInsertion (ContainerName IN Container.ContainerName%TYPE
                                  , MainQty IN Container.Qty%TYPE
                                  , SpecName IN SpecBase.SpecName%TYPE
                                  , StepLogicName IN A_StepLogic.StepLogicName%TYPE
                                  , CurrentInsertionNumber IN Container.InsertionNumber%TYPE
                                  , ProcessTypeName IN A_ProcessType.ProcessTypeName%TYPE)
RETURN VARCHAR2
;
FUNCTION VerifyMoveOut (ContainerName IN Container.ContainerName%TYPE
                        , MainQty IN Container.Qty%TYPE
                        , SpecName IN SpecBase.SpecName%TYPE
                        , StepLogicName IN A_StepLogic.StepLogicName%TYPE
                        , CurrentInsertionNumber IN Container.InsertionNumber%TYPE)
RETURN VARCHAR2
;
FUNCTION VerifyWIPData (ContainerName IN Container.ContainerName%TYPE
                        , SpecName IN SpecBase.SpecName%TYPE
                        , StepLogicName IN A_StepLogic.StepLogicName%TYPE
                        , CurrentInsertionNumber IN Container.InsertionNumber%TYPE
                        , ProcessTypeNames IN VARCHAR2
                        , WIPDataNames IN VARCHAR2
                        , WIPDataValues IN VARCHAR2)
RETURN VARCHAR2
;
FUNCTION ExecuteDisposition (ContainerName IN Container.ContainerName%TYPE
                             , StepLogicName IN A_StepLogic.StepLogicName%TYPE)
RETURN VARCHAR2
;
PROCEDURE SaveCriteria(ContainerId IN Container.ContainerId%TYPE
                       , CriteriaId IN Container.StepLogicCriteriaId%TYPE)
;
PROCEDURE SetNextInsertionDetails(ContainerId IN Container.ContainerId%TYPE)
;
PROCEDURE ClearInsertionDetails(ContainerId IN Container.ContainerId%TYPE)
;
PROCEDURE AddWIPLotData(WIPLotId IN A_WIPLot.WIPLotId%TYPE
                        , WIPDataNameName IN A_WIPLotData.WIPDataNameName%TYPE)
;
PROCEDURE AddWIPLotDetailsData(WIPLotDetailsId IN A_WIPLotDetails.WIPLotDetailsId%TYPE
                               , WIPDataNameName IN A_WIPLotDetailsData.WIPDataNameName%TYPE)
;
PROCEDURE AddWIPData(ContainerId IN A_WIPLotDetailsTempData.ContainerId%TYPE
                     , ServiceName IN A_WIPLotDetailsTempData.ServiceName%TYPE
                     , WIPDataNameName IN A_WIPDataName.WIPDataNameName%TYPE
                     , ProcessTypeName IN A_ProcessType.ProcessTypeName%TYPE DEFAULT ''
                     , DefaultValue IN A_WIPLotDetailsTempData.ServiceName%TYPE DEFAULT ''
                     , DisplaySequence IN A_WIPLotDetailsTempData.DisplaySequence%TYPE DEFAULT 1)
;
--===========================================================================
-- End of package specification
--===========================================================================
END;
/
CREATE OR REPLACE PACKAGE BODY StepLogicHandler AS

--=====================
-- Package information
--=====================
FUNCTION Version RETURN VARCHAR2 IS BEGIN RETURN ('3.4.0001'); END;

--=====================
-- InsertInsertionDetails
--=====================
PROCEDURE InsertInsertionDetails(ContainerId IN Container.ContainerId%TYPE
                                 , AllowQtyOverride IN A_WIPLotDetailsTemp.AllowQtyOverride%TYPE
                                 , QtyToProcess IN A_WIPLotDetailsTemp.QtyToProcess%TYPE
                                 , ProcessTypeName IN A_ProcessType.ProcessTypeName%TYPE
                                 , ProcessStatus IN A_WIPLotDetailsTemp.ProcessStatus%TYPE DEFAULT 'ACTIVE'
                                 , TestStatus IN A_WIPLotDetailsTemp.TestStatus%TYPE DEFAULT ''
                                 , WIPTestStatus IN A_WIPLotDetailsTemp.WIPTestStatus%TYPE DEFAULT ''
                                 , TestPlan IN A_WIPLotDetailsTemp.WIPTestStatus%TYPE DEFAULT ''
                                 , TestSubPlan IN A_WIPLotDetailsTemp.WIPTestStatus%TYPE DEFAULT ''
                                 , ValidQtys IN VARCHAR2 DEFAULT ''
                                 , MinQtyToProcess IN A_WIPLotDetailsTemp.MinQtyToProcess%TYPE DEFAULT 0
                                 , MaxQtyToProcess IN A_WIPLotDetailsTemp.MaxQtyToProcess%TYPE DEFAULT 0)
IS
    --=====================
    -- Input parameter variables
    --=====================
    vContainerId Container.ContainerId%TYPE := ContainerId;
    vAllowQtyOverride A_WIPLotDetailsTemp.AllowQtyOverride%TYPE := AllowQtyOverride;
    vQtyToProcess A_WIPLotDetailsTemp.QtyToProcess%TYPE := QtyToProcess;
    vProcessTypeName A_ProcessType.ProcessTypeName%TYPE := ProcessTypeName;
    vProcessStatus A_WIPLotDetailsTemp.ProcessStatus%TYPE := ProcessStatus;
    vTestStatus A_WIPLotDetailsTemp.TestStatus%TYPE := TestStatus;
    vWIPTestStatus A_WIPLotDetailsTemp.WIPTestStatus%TYPE := WIPTestStatus;
    vTestPlan A_WIPLotDetailsTemp.TestPlan%TYPE := TestPlan;
    vTestSubPlan A_WIPLotDetailsTemp.TestPlan%TYPE := TestSubPlan;
    cValidQtys TRGUtil.COLLECTION := TRGUtil.SetCollection(ValidQtys, ',');
    nMinQtyToProcess A_WIPLotDetailsTemp.MinQtyToProcess%TYPE := MinQtyToProcess;
    nMaxQtyToProcess A_WIPLotDetailsTemp.MaxQtyToProcess%TYPE := MaxQtyToProcess;
    --=====================
    -- Local variables
    --=====================
    vProcessTypeId A_ProcessType.ProcessTypeId%TYPE;
    vWIPLotDetailsTempId A_WIPLotDetailsTemp.WIPLotDetailsTempId%TYPE;
    nCount NUMBER;
    --=====================
    -- Pragma Declarations
    --=====================
    eExit EXCEPTION;
    PRAGMA EXCEPTION_INIT (eExit, -20000);
    eAppError EXCEPTION;
    PRAGMA EXCEPTION_INIT (eAppError, -20005);
BEGIN
    --=====================
    -- Fetch the Process Type Id
    --=====================
    SELECT
        ProcessTypeId
    INTO
        vProcessTypeId
    FROM
        A_ProcessType
    WHERE
        ProcessTypeName = vProcessTypeName
    ;
    --=====================
    -- Set the Allow Override flag properly
    --=====================
    vWIPLotDetailsTempId := SetInstanceId('WIPLotDetailsTemp');
    IF vAllowQtyOverride = 1 THEN
        vAllowQtyOverride := 1;
    ELSE
        vAllowQtyOverride := 0;
    END IF;
    --=====================
    -- Insert the yield record
    --=====================
    INSERT INTO A_WIPLotDetailsTemp
    (
        CDOTypeId
        , WIPLotDetailsTempId
        , ChangeCount
        , ContainerId
        , AllowQtyOverride
        , QtyToProcess
        , ProcessTypeId
        , ProcessStatus
        , TestStatus
        , WIPTestStatus
        , TestPlan
        , TestSubPlan
        , MinQtyToProcess
        , MaxQtyToProcess
    )
    VALUES
    (
        GetCDODefId('WIPLotDetailsTemp')
        , vWIPLotDetailsTempId
        , 1
        , vContainerId
        , vAllowQtyOverride
        , vQtyToProcess
        , vProcessTypeId
        , UPPER(NVL(vProcessStatus, 'ACTIVE'))
        , UPPER(vTestStatus)
        , UPPER(vWIPTestStatus)
        , UPPER(vTestPlan)
        , UPPER(vTestSubPlan)
        , nMinQtyToProcess
        , nMaxQtyToProcess
    );
    --=====================
    -- Insert the valid Qty To Process if exist
    --=====================
    FOR nCount IN 0..cValidQtys.COUNT-1 LOOP
        INSERT INTO A_WIPLotDetailsTempValidQtys
        (
            CDOTypeId
            , WIPLotDetailsTempValidQtysId
            , ChangeCount
            , WIPLotDetailsTempId
            , QtyToProcess
            , DisplaySequence
        )
        VALUES
        (
            GetCDODefId('WIPLotDetailsTempValidQtys')
            , SetInstanceId('WIPLotDetailsTempValidQtys')
            , 1
            , vWIPLotDetailsTempId
            , cValidQtys(nCount)
            , nCount
        );
    END LOOP;
EXCEPTION
    WHEN eExit THEN
        ROLLBACK;
        RAISE_APPLICATION_ERROR (-20005, 'StepLogicHandler.InsertInsertionDetails->Exited prematurely');
    WHEN eAppError THEN
        ROLLBACK;
        RAISE_APPLICATION_ERROR (-20005, SQLERRM);
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE_APPLICATION_ERROR (-20005, 'StepLogicHandler.InsertInsertionDetails->' || SQLERRM);
END InsertInsertionDetails;

--=====================
-- SetInsertionDetails
--=====================
FUNCTION SetInsertionDetails (ContainerName IN Container.ContainerName%TYPE
                              , MainQty IN Container.Qty%TYPE
                              , SpecName IN SpecBase.SpecName%TYPE
                              , StepLogicName IN A_StepLogic.StepLogicName%TYPE
                              , CurrentInsertionNumber IN Container.InsertionNumber%TYPE
                              , ProcessTypeNames IN VARCHAR2
                              , ProcessStatuses IN VARCHAR2)
RETURN VARCHAR2 IS
    --=====================
    -- Input parameter variables
    --=====================
    vContainerName Container.ContainerName%TYPE := ContainerName;
    nMainQty Container.Qty%TYPE := MainQty;
    vSpecName SpecBase.SpecName%TYPE := SpecName;
    vStepLogicName A_StepLogic.StepLogicName%TYPE := UPPER(StepLogicName);
    nCurrentInsertionNumber Container.InsertionNumber%TYPE := CurrentInsertionNumber;
    cProcessTypeNames TRGUtil.COLLECTION;
    cProcessStatuses TRGUtil.COLLECTION;
    --=====================
    -- Cursor Declarations
    --=====================
    CURSOR curLot IS
    SELECT
        ContainerId
        , CurrentWIPLotId
        , StepLogicCriteriaId
    FROM
        Container
    WHERE
        ContainerName = vContainerName
    ;
    --=====================
    -- Local variables
    --=====================
    nNextInsertionNumber Container.InsertionNumber%TYPE := nCurrentInsertionNumber + 1;
    rLot curLot%ROWTYPE;
    nCount NUMBER;
    vResult VARCHAR2(1000) := '0';
    --=====================
    -- Pragma Declarations
    --=====================
    PRAGMA AUTONOMOUS_TRANSACTION;
    eExit EXCEPTION;
    PRAGMA EXCEPTION_INIT (eExit, -20000);
    eAppError EXCEPTION;
    PRAGMA EXCEPTION_INIT (eAppError, -20005);
BEGIN
    --=====================
    -- Fetch lot information
    --=====================
    OPEN curLot;
    FETCH curLot INTO rLot;
    IF NOT curLot%NOTFOUND THEN
        CLOSE curLot;
        --=====================
        -- Ensure there are no data already set (data may exist due to application errors)
        --=====================
        SELECT
            COUNT(*)
        INTO
            nCount
        FROM
            A_WIPLotDetailsTemp
        WHERE
            ContainerId = rLot.ContainerId
        ;
        IF nCount = 0 THEN
            --=====================
            -- Select the Step Logic
            --=====================
            IF vStepLogicName = 'LOTSIZESAMPLING' THEN
                SELECT LotSizeSampling.SetInsertionDetails
                (
                    vContainerName
                    , rLot.ContainerId
                    , rLot.CurrentWIPLotId
                    , rLot.StepLogicCriteriaId
                    , nMainQty
                    , vSpecName
                    , vStepLogicName
                    , nNextInsertionNumber
                    , ProcessTypeNames
                    , ProcessStatuses
                ) INTO vResult FROM DUAL;
            ELSE
                --=====================
                -- Set the Process Type Names and Statuses
                --=====================
                cProcessTypeNames := TRGUtil.SetCollection(ProcessTypeNames, ',');
                cProcessStatuses := TRGUtil.SetCollection(ProcessStatuses, ',');
                --=====================
                -- Insert the given Process Types
                --=====================
                FOR nCount IN 0..cProcessTypeNames.COUNT-1 LOOP
                    InsertInsertionDetails (rLot.ContainerId
                                            , 1
                                            , nMainQty
                                            , cProcessTypeNames(nCount)
                                            , cProcessStatuses(nCount)
                                            , ''
                                            , '');
                END LOOP;
            END IF;
        END IF;
    ELSE
        CLOSE curLot;
    END IF;
    --=====================
    -- Commit the data
    --=====================
    COMMIT;
    --=====================
    -- Return success
    --=====================
    RETURN (vResult);
EXCEPTION
    WHEN eExit THEN
        ROLLBACK;
        RETURN ('StepLogicHandler.SetInsertionDetails->Exited prematurely');
    WHEN eAppError THEN
        ROLLBACK;
        RETURN (REPLACE(SQLERRM, 'ORA-20005: '));
    WHEN OTHERS THEN
        ROLLBACK;
        RETURN ('StepLogicHandler.SetInsertionDetails->' || SQLERRM);
END SetInsertionDetails;

--=====================
-- VerifyCompleteInsertion
--=====================
FUNCTION VerifyCompleteInsertion (ContainerName IN Container.ContainerName%TYPE
                                  , MainQty IN Container.Qty%TYPE
                                  , SpecName IN SpecBase.SpecName%TYPE
                                  , StepLogicName IN A_StepLogic.StepLogicName%TYPE
                                  , CurrentInsertionNumber IN Container.InsertionNumber%TYPE
                                  , ProcessTypeName IN A_ProcessType.ProcessTypeName%TYPE)
RETURN VARCHAR2 IS
    --=====================
    -- Input parameter variables
    --=====================
    vContainerName Container.ContainerName%TYPE := ContainerName;
    nMainQty Container.Qty%TYPE := MainQty;
    vSpecName SpecBase.SpecName%TYPE := SpecName;
    vStepLogicName A_StepLogic.StepLogicName%TYPE := UPPER(StepLogicName);
    nCurrentInsertionNumber Container.InsertionNumber%TYPE := CurrentInsertionNumber;
    vProcessTypeName A_ProcessType.ProcessTypeName%TYPE := ProcessTypeName;
    --=====================
    -- Cursor Declarations
    --=====================
    CURSOR curLot IS
    SELECT
        ContainerId
        , CurrentWIPLotId
        , StepLogicCriteriaId
    FROM
        Container
    WHERE
        ContainerName = vContainerName
    ;
    --=====================
    -- Local variables
    --=====================
    rLot curLot%ROWTYPE;
    vResult VARCHAR2(1000) := '0';
    --=====================
    -- Pragma Declarations
    --=====================
    eExit EXCEPTION;
    PRAGMA EXCEPTION_INIT (eExit, -20000);
    eAppError EXCEPTION;
    PRAGMA EXCEPTION_INIT (eAppError, -20005);
BEGIN
    --=====================
    -- Fetch lot information
    --=====================
    OPEN curLot;
    FETCH curLot INTO rLot;
    IF NOT curLot%NOTFOUND THEN
        CLOSE curLot;
        --=====================
        -- Select the Step Logic
        --=====================
        IF vStepLogicName = 'LOTSIZESAMPLING' THEN
            SELECT LotSizeSampling.VerifyCompleteInsertion
            (
                vContainerName
                , rLot.ContainerId
                , rLot.CurrentWIPLotId
                , rLot.StepLogicCriteriaId
                , nMainQty
                , vSpecName
                , vStepLogicName
                , nCurrentInsertionNumber
                , vProcessTypeName
            ) INTO vResult FROM DUAL;
        END IF;
    ELSE
        CLOSE curLot;
    END IF;
    --=====================
    -- Return success
    --=====================
    RETURN (vResult);
EXCEPTION
    WHEN eExit THEN
        RETURN ('StepLogicHandler.VerifyCompleteInsertion->Exited prematurely');
    WHEN eAppError THEN
        RETURN (REPLACE(SQLERRM, 'ORA-20005: '));
    WHEN OTHERS THEN
        RETURN ('StepLogicHandler.VerifyCompleteInsertion->' || SQLERRM);
END VerifyCompleteInsertion;

--=====================
-- VerifyMoveOut
--=====================
FUNCTION VerifyMoveOut (ContainerName IN Container.ContainerName%TYPE
                        , MainQty IN Container.Qty%TYPE
                        , SpecName IN SpecBase.SpecName%TYPE
                        , StepLogicName IN A_StepLogic.StepLogicName%TYPE
                        , CurrentInsertionNumber IN Container.InsertionNumber%TYPE)
RETURN VARCHAR2 IS
    --=====================
    -- Input parameter variables
    --=====================
    vContainerName Container.ContainerName%TYPE := ContainerName;
    nMainQty Container.Qty%TYPE := MainQty;
    vSpecName SpecBase.SpecName%TYPE := SpecName;
    vStepLogicName A_StepLogic.StepLogicName%TYPE := UPPER(StepLogicName);
    nCurrentInsertionNumber Container.InsertionNumber%TYPE := CurrentInsertionNumber;
    --=====================
    -- Cursor Declarations
    --=====================
    CURSOR curLot IS
    SELECT
        ContainerId
        , CurrentWIPLotId
        , StepLogicCriteriaId
    FROM
        Container
    WHERE
        ContainerName = vContainerName
    ;
    --=====================
    -- Local variables
    --=====================
    rLot curLot%ROWTYPE;
    vResult VARCHAR2(1000) := '0';
    --=====================
    -- Pragma Declarations
    --=====================
    eExit EXCEPTION;
    PRAGMA EXCEPTION_INIT (eExit, -20000);
    eAppError EXCEPTION;
    PRAGMA EXCEPTION_INIT (eAppError, -20005);
BEGIN
    --=====================
    -- Fetch lot information
    --=====================
    OPEN curLot;
    FETCH curLot INTO rLot;
    IF NOT curLot%NOTFOUND THEN
        CLOSE curLot;
        --=====================
        -- Select the Step Logic
        --=====================
        IF vStepLogicName = 'LOTSIZESAMPLING' THEN
            SELECT LotSizeSampling.VerifyMoveOut
            (
                vContainerName
                , rLot.ContainerId
                , rLot.CurrentWIPLotId
                , rLot.StepLogicCriteriaId
                , nMainQty
                , vSpecName
                , vStepLogicName
                , nCurrentInsertionNumber
            ) INTO vResult FROM DUAL;
        END IF;
    ELSE
        CLOSE curLot;
    END IF;
    --=====================
    -- Return success
    --=====================
    RETURN (vResult);
EXCEPTION
    WHEN eExit THEN
        RETURN ('StepLogicHandler.VerifyMoveOut->Exited prematurely');
    WHEN eAppError THEN
        RETURN (REPLACE(SQLERRM, 'ORA-20005: '));
    WHEN OTHERS THEN
        RETURN ('StepLogicHandler.VerifyMoveOut->' || SQLERRM);
END VerifyMoveOut;

--=====================
-- VerifyWIPData
--=====================
FUNCTION VerifyWIPData (ContainerName IN Container.ContainerName%TYPE
                        , SpecName IN SpecBase.SpecName%TYPE
                        , StepLogicName IN A_StepLogic.StepLogicName%TYPE
                        , CurrentInsertionNumber IN Container.InsertionNumber%TYPE
                        , ProcessTypeNames IN VARCHAR2
                        , WIPDataNames IN VARCHAR2
                        , WIPDataValues IN VARCHAR2)
RETURN VARCHAR2 IS
    --=====================
    -- Input parameter variables
    --=====================
    vContainerName Container.ContainerName%TYPE := ContainerName;
    vSpecName SpecBase.SpecName%TYPE := SpecName;
    vStepLogicName A_StepLogic.StepLogicName%TYPE := UPPER(StepLogicName);
    nCurrentInsertionNumber Container.InsertionNumber%TYPE := CurrentInsertionNumber;
    --=====================
    -- Cursor Declarations
    --=====================
    CURSOR curLot IS
    SELECT
        ContainerId
        , CurrentWIPLotId
        , StepLogicCriteriaId
    FROM
        Container
    WHERE
        ContainerName = vContainerName
    ;
    --=====================
    -- Local variables
    --=====================
    rLot curLot%ROWTYPE;
    vResult VARCHAR2(1000) := '0';
    --=====================
    -- Pragma Declarations
    --=====================
    eExit EXCEPTION;
    PRAGMA EXCEPTION_INIT (eExit, -20000);
    eAppError EXCEPTION;
    PRAGMA EXCEPTION_INIT (eAppError, -20005);
BEGIN
    --=====================
    -- Fetch lot information
    --=====================
    OPEN curLot;
    FETCH curLot INTO rLot;
    IF NOT curLot%NOTFOUND THEN
        CLOSE curLot;
        --=====================
        -- Select the Step Logic
        --=====================
        IF vStepLogicName = 'LOTSIZESAMPLINE' THEN
            SELECT LotSizeSampling.VerifyWIPData
            (
                vContainerName
                , rLot.ContainerId
                , rLot.CurrentWIPLotId
                , rLot.StepLogicCriteriaId
                , vSpecName
                , vStepLogicName
                , nCurrentInsertionNumber
                , ProcessTypeNames
                , WIPDataNames
                , WIPDataValues
            ) INTO vResult FROM DUAL;
        END IF;
    ELSE
        CLOSE curLot;
    END IF;
    --=====================
    -- Return success
    --=====================
    RETURN (vResult);
EXCEPTION
    WHEN eExit THEN
        RETURN ('StepLogicHandler.VerifyWIPData->Exited prematurely');
    WHEN eAppError THEN
        RETURN (REPLACE(SQLERRM, 'ORA-20005: '));
    WHEN OTHERS THEN
        RETURN ('StepLogicHandler.VerifyWIPData->' || SQLERRM);
END VerifyWIPData;

--=====================
-- ExecuteDisposition
--=====================
FUNCTION ExecuteDisposition (ContainerName IN Container.ContainerName%TYPE
                             , StepLogicName IN A_StepLogic.StepLogicName%TYPE)
RETURN VARCHAR2 IS
    --=====================
    -- Input parameter variables
    --=====================
    vContainerName Container.ContainerName%TYPE := ContainerName;
    vStepLogicName A_StepLogic.StepLogicName%TYPE := StepLogicName;
    --=====================
    -- Cursor Declarations
    --=====================
    CURSOR curLot IS
    SELECT
        ContainerId
        , CurrentWIPLotId
        , StepLogicCriteriaId
        , LotDispositionId
    FROM
        Container
    WHERE
        ContainerName = vContainerName
    ;
    --=====================
    -- Local variables
    --=====================
    rLot curLot%ROWTYPE;
    vResult VARCHAR2(1000) := '0';
    --=====================
    -- Pragma Declarations
    --=====================
    eExit EXCEPTION;
    PRAGMA EXCEPTION_INIT (eExit, -20000);
    eAppError EXCEPTION;
    PRAGMA EXCEPTION_INIT (eAppError, -20005);
BEGIN
    --=====================
    -- Fetch lot information
    --=====================
    OPEN curLot;
    FETCH curLot INTO rLot;
    IF NOT curLot%NOTFOUND THEN
        CLOSE curLot;
        --=====================
        -- Select the Step Logic
        --=====================
        IF vStepLogicName = 'ABC' THEN
            vResult := '0';
        END IF;
    ELSE
        CLOSE curLot;
    END IF;
    --=====================
    -- Return success
    --=====================
    RETURN (vResult);
EXCEPTION
    WHEN eExit THEN
        RETURN ('StepLogicHandler.ExecuteDisposition->Exited prematurely');
    WHEN eAppError THEN
        RETURN (REPLACE(SQLERRM, 'ORA-20005: '));
    WHEN OTHERS THEN
        RETURN ('StepLogicHandler.ExecuteDisposition->' || SQLERRM);
END ExecuteDisposition;

--=====================
-- SaveCriteria
--=====================
PROCEDURE SaveCriteria(ContainerId IN Container.ContainerId%TYPE
                       , CriteriaId IN Container.StepLogicCriteriaId%TYPE)
IS
    --=====================
    -- Input parameter variables
    --=====================
    vContainerId Container.ContainerId%TYPE := ContainerId;
    vCriteriaId Container.StepLogicCriteriaId%TYPE := CriteriaId;
    --=====================
    -- Local variables
    --=====================

    --=====================
    -- Pragma Declarations
    --=====================
    eExit EXCEPTION;
    PRAGMA EXCEPTION_INIT (eExit, -20000);
    eAppError EXCEPTION;
    PRAGMA EXCEPTION_INIT (eAppError, -20005);
BEGIN
    --=====================
    -- Save the criteria
    --=====================
    UPDATE
        Container
    SET
        StepLogicCriteriaId = vCriteriaId
    WHERE
        ContainerId = vContainerId
    ;
EXCEPTION
    WHEN eExit THEN
        ROLLBACK;
        RAISE_APPLICATION_ERROR (-20005, 'StepLogicHandler.SaveCriteria->Exited prematurely');
    WHEN eAppError THEN
        ROLLBACK;
        RAISE_APPLICATION_ERROR (-20005, SQLERRM);
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE_APPLICATION_ERROR (-20005, 'StepLogicHandler.SaveCriteria->' || SQLERRM);
END SaveCriteria;

--=====================
-- SetNextInsertionDetails
--=====================
PROCEDURE SetNextInsertionDetails(ContainerId IN Container.ContainerId%TYPE)
IS
    --=====================
    -- Input parameter variables
    --=====================
    vContainerId Container.ContainerId%TYPE := ContainerId;
    --=====================
    -- Cursor declarations
    --=====================
    CURSOR curWIPLotDetails IS
    SELECT
        DECODE(A.Qty, 0, A.Qty2, A.Qty) MainQty
        , B.*
    FROM
        Container A
        , A_WIPLotDetails B
    WHERE
        A.CurrentWIPLotId = B.WIPLotId
        AND A.ContainerId = vContainerId
    ;
    --=====================
    -- Local variables
    --=====================
    rWIPLotDetails curWIPLotDetails%ROWTYPE;
    vProcessStatus A_WIPLotDetailsTemp.ProcessStatus%TYPE;
    nQtyToProcess NUMBER;
    --=====================
    -- Pragma Declarations
    --=====================
    eExit EXCEPTION;
    PRAGMA EXCEPTION_INIT (eExit, -20000);
    eAppError EXCEPTION;
    PRAGMA EXCEPTION_INIT (eAppError, -20005);
BEGIN
    --=====================
    -- Set the next insertion details - i.e. insertion 2 onwards
    --=====================
    FOR rWIPLotDetails IN curWIPLotDetails LOOP
        IF rWIPLotDetails.ProcessStatus = 'NR' THEN
            vProcessStatus := 'NR';
        ELSE
            vProcessStatus := 'ACTIVE';
        END IF;
        nQtyToProcess := rWIPLotDetails.QtyToProcess - rWIPLotDetails.ProcessRejectQty;
        IF nQtyToProcess > rWIPLotDetails.MainQty THEN
            nQtyToProcess := rWIPLotDetails.MainQty;
        END IF;
        InsertInsertionDetails (vContainerId
                                , 1
                                , nQtyToProcess
                                , rWIPLotDetails.ProcessTypeName
                                , vProcessStatus
                                , rWIPLotDetails.TestStatus
                                , rWIPLotDetails.WIPTestStatus
                                , rWIPLotDetails.TestPlan
                                , rWIPLotDetails.TestSubPlan);
    END LOOP;
EXCEPTION
    WHEN eExit THEN
        ROLLBACK;
        RAISE_APPLICATION_ERROR (-20005, 'StepLogicHandler.SetNextInsertionDetails->Exited prematurely');
    WHEN eAppError THEN
        ROLLBACK;
        RAISE_APPLICATION_ERROR (-20005, SQLERRM);
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE_APPLICATION_ERROR (-20005, 'StepLogicHandler.SetNextInsertionDetails->' || SQLERRM);
END SetNextInsertionDetails;

--=====================
-- ClearInsertionDetails
--=====================
PROCEDURE ClearInsertionDetails(ContainerId IN Container.ContainerId%TYPE)
IS
    --=====================
    -- Input parameter variables
    --=====================
    vContainerId Container.ContainerId%TYPE := ContainerId;
    --=====================
    -- Local variables
    --=====================

    --=====================
    -- Pragma Declarations
    --=====================
    eExit EXCEPTION;
    PRAGMA EXCEPTION_INIT (eExit, -20000);
    eAppError EXCEPTION;
    PRAGMA EXCEPTION_INIT (eAppError, -20005);
BEGIN
    --=====================
    -- Clear insertion details
    --=====================
    DELETE
        A_WIPLotDetailsTemp
    WHERE
        ContainerId = vContainerId
    ;
EXCEPTION
    WHEN eExit THEN
        ROLLBACK;
        RAISE_APPLICATION_ERROR (-20005, 'StepLogicHandler.ClearInsertionDetails->Exited prematurely');
    WHEN eAppError THEN
        ROLLBACK;
        RAISE_APPLICATION_ERROR (-20005, SQLERRM);
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE_APPLICATION_ERROR (-20005, 'StepLogicHandler.ClearInsertionDetails->' || SQLERRM);
END ClearInsertionDetails;

--=====================
-- AddWIPLotData
--=====================
PROCEDURE AddWIPLotData(WIPLotId IN A_WIPLot.WIPLotId%TYPE
                        , WIPDataNameName IN A_WIPLotData.WIPDataNameName%TYPE)
IS
    --=====================
    -- Input parameter variables
    --=====================
    vWIPLotId A_WIPLot.WIPLotId%TYPE := WIPLotId;
    vWIPDataNameName A_WIPLotData.WIPDataNameName%TYPE := WIPDataNameName;
    --=====================
    -- Cursor Declarations
    --=====================
    CURSOR curWIPDataName IS
    SELECT
        WIPDataNameId
    FROM
        A_WIPDataName
    WHERE
        WIPDataNameName = vWIPDataNameName
    ;
    --=====================
    -- Local variables
    --=====================
    nCount NUMBER;
    rWIPDataName curWIPDataName%ROWTYPE;
    --=====================
    -- Pragma Declarations
    --=====================
    eExit EXCEPTION;
    PRAGMA EXCEPTION_INIT (eExit, -20000);
    eAppError EXCEPTION;
    PRAGMA EXCEPTION_INIT (eAppError, -20005);
BEGIN
    --=====================
    -- Check whether the WIP Data already exists
    --=====================
    SELECT
        COUNT(*)
    INTO
        nCount
    FROM
        A_WIPLotData
    WHERE
        WIPLotId = vWIPLotId
        AND ServiceName = 'LotMoveOut'
        AND WIPDataNameName = vWIPDataNameName
    ;
    IF nCount = 0 THEN
        --=====================
        -- Find the WIP Data Name setup
        --=====================
        OPEN curWIPDataName;
        FETCH curWIPDataName INTO rWIPDataName;
        IF curWIPDataName%NOTFOUND THEN
            CLOSE curWIPDataName;
            RAISE_APPLICATION_ERROR (-20005, 'StepLogicHandler_E1001 ' || vWIPDataNameName);
        END IF;
        CLOSE curWIPDataName;
        --=====================
        -- Get the number records in wIP Lot Data
        --=====================
        SELECT
            COUNT(*)
        INTO
            nCount
        FROM
            A_WIPLotData
        WHERE
            WIPLotId = vWIPLotId
        ;
        --=====================
        -- Insert the record
        --=====================
        INSERT INTO
            A_WIPLotData
        (
            WIPLotDataId
            , CDOTypeId
            , ChangeCount
            , WIPLotId
            , ServiceName
            , WIPDataNameId
            , WIPDataNameName
            , DisplaySequence
        )
        VALUES
        (
            SetInstanceId('WIPLotData')
            , GetCDODefId('WIPLotData')
            , 1
            , vWIPLotId
            , 'LotMoveOut'
            , rWIPDataName.WIPDataNameId
            , vWIPDataNameName
            , nCount + 1
        );
    END IF;
EXCEPTION
    WHEN eExit THEN
        RAISE_APPLICATION_ERROR (-20005, 'StepLogicHandler.AddWIPLotData->Exited prematurely');
    WHEN eAppError THEN
        RAISE_APPLICATION_ERROR (-20005, SQLERRM);
    WHEN OTHERS THEN
        RAISE_APPLICATION_ERROR (-20005, 'StepLogicHandler.AddWIPLotData->' || SQLERRM);
END AddWIPLotData;

--=====================
-- AddWIPLotDetailsData
--=====================
PROCEDURE AddWIPLotDetailsData(WIPLotDetailsId IN A_WIPLotDetails.WIPLotDetailsId%TYPE
                               , WIPDataNameName IN A_WIPLotDetailsData.WIPDataNameName%TYPE)
IS
    --=====================
    -- Input parameter variables
    --=====================
    vWIPLotDetailsId A_WIPLotDetails.WIPLotDetailsId%TYPE := WIPLotDetailsId;
    vWIPDataNameName A_WIPLotDetailsData.WIPDataNameName%TYPE := WIPDataNameName;
    --=====================
    -- Cursor Declarations
    --=====================
    CURSOR curWIPDataName IS
    SELECT
        WIPDataNameId
    FROM
        A_WIPDataName
    WHERE
        WIPDataNameName = vWIPDataNameName
    ;
    --=====================
    -- Local variables
    --=====================
    nCount NUMBER;
    rWIPDataName curWIPDataName%ROWTYPE;
    --=====================
    -- Pragma Declarations
    --=====================
    eExit EXCEPTION;
    PRAGMA EXCEPTION_INIT (eExit, -20000);
    eAppError EXCEPTION;
    PRAGMA EXCEPTION_INIT (eAppError, -20005);
BEGIN
    --=====================
    -- Check whether the WIP Data already exists
    --=====================
    SELECT
        COUNT(*)
    INTO
        nCount
    FROM
        A_WIPLotDetailsData
    WHERE
        WIPLotDetailsId = vWIPLotDetailsId
        AND ServiceName = 'LotMoveOut'
        AND WIPDataNameName = vWIPDataNameName
    ;
    IF nCount = 0 THEN
        --=====================
        -- Find the WIP Data Name setup
        --=====================
        OPEN curWIPDataName;
        FETCH curWIPDataName INTO rWIPDataName;
        IF curWIPDataName%NOTFOUND THEN
            CLOSE curWIPDataName;
            RAISE_APPLICATION_ERROR (-20005, 'StepLogicHandler_E1001 ' || vWIPDataNameName);
        END IF;
        CLOSE curWIPDataName;
        --=====================
        -- Get the number records in wIP Lot Data
        --=====================
        SELECT
            COUNT(*)
        INTO
            nCount
        FROM
            A_WIPLotDetailsData
        WHERE
            WIPLotDetailsId = vWIPLotDetailsId
        ;
        --=====================
        -- Insert the record
        --=====================
        INSERT INTO
            A_WIPLotDetailsData
        (
            WIPLotDetailsDataId
            , CDOTypeId
            , ChangeCount
            , WIPLotDetailsId
            , ServiceName
            , WIPDataNameId
            , WIPDataNameName
            , DisplaySequence
        )
        VALUES
        (
            SetInstanceId('WIPLotDetailsData')
            , GetCDODefId('WIPLotDetailsData')
            , 1
            , vWIPLotDetailsId
            , 'LotMoveOut'
            , rWIPDataName.WIPDataNameId
            , vWIPDataNameName
            , nCount + 1
        );
    END IF;
EXCEPTION
    WHEN eExit THEN
        RAISE_APPLICATION_ERROR (-20005, 'StepLogicHandler.AddWIPLotDetailsData->Exited prematurely');
    WHEN eAppError THEN
        RAISE_APPLICATION_ERROR (-20005, SQLERRM);
    WHEN OTHERS THEN
        RAISE_APPLICATION_ERROR (-20005, 'StepLogicHandler.AddWIPLotDetailsData->' || SQLERRM);
END AddWIPLotDetailsData;

--=====================
-- AddWIPData
--=====================
PROCEDURE AddWIPData(ContainerId IN A_WIPLotDetailsTempData.ContainerId%TYPE
                     , ServiceName IN A_WIPLotDetailsTempData.ServiceName%TYPE
                     , WIPDataNameName IN A_WIPDataName.WIPDataNameName%TYPE
                     , ProcessTypeName IN A_ProcessType.ProcessTypeName%TYPE DEFAULT ''
                     , DefaultValue IN A_WIPLotDetailsTempData.ServiceName%TYPE DEFAULT ''
                     , DisplaySequence IN A_WIPLotDetailsTempData.DisplaySequence%TYPE DEFAULT 1)
IS
    --=====================
    -- Input parameter variables
    --=====================
    vContainerId A_WIPLotDetailsTempData.ContainerId%TYPE := ContainerId;
    vServiceName A_WIPLotDetailsTempData.ServiceName%TYPE := ServiceName;
    vWIPDataNameName A_WIPDataName.WIPDataNameName%TYPE := WIPDataNameName;
    vProcessTypeName A_ProcessType.ProcessTypeName%TYPE := ProcessTypeName;
    vDefaultValue A_WIPLotDetailsTempData.ServiceName%TYPE := DefaultValue;
    nDisplaySequence A_WIPLotDetailsTempData.DisplaySequence%TYPE := DisplaySequence;
    --=====================
    -- Cursor Declarations
    --=====================
    CURSOR curWIPDataName IS
    SELECT
        WIPDataNameId
    FROM
        A_WIPDataName
    WHERE
        WIPDataNameName = vWIPDataNameName
    ;
    CURSOR curProcessType IS
    SELECT
        ProcessTypeId
    FROM
        A_ProcessType
    WHERE
        ProcessTypeName = vProcessTypeName
    ;
    --=====================
    -- Local variables
    --=====================
    nCount NUMBER;
    rWIPDataName curWIPDataName%ROWTYPE;
    rProcessType curProcessType%ROWTYPE;
    vProcessTypeId A_ProcessType.ProcessTypeId%TYPE;
    --=====================
    -- Pragma Declarations
    --=====================
    eExit EXCEPTION;
    PRAGMA EXCEPTION_INIT (eExit, -20000);
    eAppError EXCEPTION;
    PRAGMA EXCEPTION_INIT (eAppError, -20005);
BEGIN
    --=====================
    -- Find the WIP Data Name setup
    --=====================
    OPEN curWIPDataName;
    FETCH curWIPDataName INTO rWIPDataName;
    IF curWIPDataName%NOTFOUND THEN
        CLOSE curWIPDataName;
        RAISE_APPLICATION_ERROR (-20005, 'StepLogicHandler_E1001 ' || vWIPDataNameName);
    END IF;
    CLOSE curWIPDataName;
    --=====================
    -- Find the Process Type setup
    --=====================
    IF vProcessTypeName IS NOT NULL THEN
        OPEN curProcessType;
        FETCH curProcessType INTO rProcessType;
        IF curProcessType%NOTFOUND THEN
            CLOSE curProcessType;
            RAISE_APPLICATION_ERROR (-20005, 'StepLogicHandler_E1002 ' || vProcessTypeName);
        ELSE
            CLOSE curProcessType;
            vProcessTypeId := rProcessType.ProcessTypeId;
        END IF;
    END IF;
    --=====================
    -- Get the number records in wIP Lot Details Temp Data
    --=====================
    SELECT
        COUNT(*)
    INTO
        nCount
    FROM
        A_WIPLotDetailsTempData
    WHERE
        ContainerId = vContainerId
    ;
    --=====================
    -- Insert the record
    --=====================
    INSERT INTO
        A_WIPLotDetailsTempData
    (
        WIPLotDetailsTempDataId
        , CDOTypeId
        , ChangeCount
        , ContainerId
        , ServiceName
        , WIPDataNameId
        , DefaultValue
        , DisplaySequence
    )
    VALUES
    (
        SetInstanceId('WIPLotDetailsTempData')
        , GetCDODefId('WIPLotDetailsTempData')
        , 1
        , vContainerId
        , vServiceName
        , rWIPDataName.WIPDataNameId
        , vDefaultvalue
        , nCount + 1
    );
EXCEPTION
    WHEN eExit THEN
        RAISE_APPLICATION_ERROR (-20005, 'StepLogicHandler.AddWIPData->Exited prematurely');
    WHEN eAppError THEN
        RAISE_APPLICATION_ERROR (-20005, SQLERRM);
    WHEN OTHERS THEN
        RAISE_APPLICATION_ERROR (-20005, 'StepLogicHandler.AddWIPData->' || SQLERRM);
END AddWIPData;

--===========================================================================
-- End of package body
--===========================================================================
END;
/
